package bd.proiect;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;


/*
 * 	Proiect BD
 * 
 * 	Database : proiect
 * 	Tables :	family
 * 				origin
 * 				genus
 * 				species
 * 				profile
 * 
 */

public class DataBase extends SQLiteOpenHelper{
	
    private static String DB_PATH = "/data/data/bd.proiect/databases/";
    private static String DB_NAME = "proiect";
    private SQLiteDatabase myDataBase; 
    private final Context myContext;
    
    public DataBase(Context context) {
    	 
    	super(context, DB_NAME, null, 1);
        this.myContext = context;
        
    }	
    
    // Daca baza de date nu exista este creata, daca exista este copiata.
    public void createDataBase() throws IOException{
    	 
    	boolean dbExist = checkDataBase();
 
    	if(dbExist){
    		// baza de date exista.
    	}else{
    		// Creaza noua baza de date in folderul default
        	this.getReadableDatabase();

        	try {
    			copyDataBase();
    		} catch (IOException e) {
 
        		throw new Error("Error copying database");
 
        	}
    	}
 
    }
 

    /*
     * Verifica daca baza de date exista , daca exista o deschide si 
     * returneaza true.
     * Evita crearea unei baze de date noi. 
     */
    private boolean checkDataBase(){
 
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
    	}catch(SQLiteException e){ }
 
    	if(checkDB != null)
    		checkDB.close();
 
    	return checkDB != null ? true : false;
    }

    
    /*
     * 	Copiaza baza de date din folderul localc assets in baza
     * 	de date nou creata in folderul de sistem.
     * 	Acum poate fi accesata si modificata.
     * 	Copierea se face prin transfer bytestream.
     */
    private void copyDataBase() throws IOException{
 
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
    	String outFileName = DB_PATH + DB_NAME;
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
 
    public void openDataBase() throws SQLException{
 
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }
 
    @Override
	public synchronized void close() {
 
    	    if(myDataBase != null)
    		    myDataBase.close();
 
    	    super.close();
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {

	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}
	
	// Inserarea familiei in tabela
	public void insertFamily(String name){
		String TableName = "family";
		myDataBase.execSQL("INSERT INTO " + TableName +
				" VALUES ( NULL ," + "'"+ name + "');");
		
	}
	
	// Inserarea Originii in tabela
	public void insertOrigin(String name){
		String TableName = "origin";
		myDataBase.execSQL("INSERT INTO " + TableName +
				" VALUES ( NULL ," + "'"+ name + "');");
		
	}
	
	// Introducere Genus, foloseste Family ID si Origin ID din Autocomplete
	public void insertGenus(String name, int family_id, int origin_id,
			String soil, String light, String temp, String water ){
		String TableName = "genus";
		myDataBase.execSQL("INSERT INTO " + TableName +
				" VALUES ( NULL ," + "'"+ name + "',"+ family_id + "," + origin_id 
				+ ",'"+ soil + "','"+ light + "','"+ temp + "','"+water+"');");
		
	}
	
	// Inserarea Speciilor in Tabela
	public void insertSpecies(String name, int genus_id, int growing_ease,
			boolean flowering, boolean foliage, String description){
			String TableName = "species";
			myDataBase.execSQL("INSERT INTO " + TableName +
					" VALUES ( NULL ," + "'"+ name + "',"+ genus_id + "," +
					growing_ease + ",'" + flowering + "','" + foliage + "','" +
					description + "');");
	}
	
	// Inserarea profilului in Tabela
	public void insertProfile(String name, int species_id, String location, 
			int foliage){
		String TableName = "species";
		myDataBase.execSQL("INSERT INTO " + TableName +
				" VALUES ( NULL ," + "'"+ name + "',"+ species_id +
				",'" + location + "'," + foliage + ");");
	}
	
	// Construieste un String cu Originile
	public String getOrigins(){
		String man = "";
		String newline = System.getProperty("line.separator");
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "origin", null);
		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				man = man + f1 + newline;
			} while (c.moveToNext());
		}
		return man;
	}
	// Construieste un String cu toate numele de familie
	public String getFamily(){
		String man = "";
		String newline = System.getProperty("line.separator");
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "family", null);
		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				man = man + f1 + newline;
			} while (c.moveToNext());
		}
		return man;
	}
	
	public ArrayList<String> getFamArray(){
		ArrayList<String> list = new ArrayList<String>();
		
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "family", null);		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				list.add(f1);
			} while (c.moveToNext());
		}
		return list;
	}
	
	// Obtinerea unui Array cu Originile, folosit pentru Autocomplete
	public ArrayList<String> getOriArray(){
		ArrayList<String> list = new ArrayList<String>();
		
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "origin", null);		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				list.add(f1);
			} while (c.moveToNext());
		}
		return list;
		
	}
	
	// Obtinerea unui Array cu Specii, folosit pentru Autocomplete
	public ArrayList<String> getSpecArray(){
		ArrayList<String> list = new ArrayList<String>();
		
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "species", null);		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				list.add(f1);
			} while (c.moveToNext());
		}
		return list;
		
	}
	
	// Obtinerea ID-ului unei familii, stiind numele
	public int getFamilyID(String name){
		int no = 0;
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "family", null);		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				if(f1.contains(name))
					no = c.getInt(0);
			} while (c.moveToNext());
		}
		
		return no;
	}
	
	// Obtinerea ID-ului unei Origini, stiind numele
	public int getOriginID(String name){
		int no = 0;
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "origin", null);		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				if(f1.contains(name))
					no = c.getInt(0);
			} while (c.moveToNext());
		}
		
		return no;
		
	}
	
	// Obtinerea ID-ului unei Specii, stiind numele
	public int getSpecID(String name){
		int no = 0;
		Cursor c = myDataBase.rawQuery("SELECT * FROM " + "species", null);		
		c.moveToFirst();
		
		if (c != null) {
			do {
				String f1 = c.getString(1);
				if(f1.contains(name))
					no = c.getInt(0);
			} while (c.moveToNext());
		}
		
		return no;
		
	}
	
	public String getSpeciesInfo(){
		String man = "";
		String newline = System.getProperty("line.separator");
			Cursor c = myDataBase.rawQuery("SELECT * FROM " + "species", null);
		//	Cursor c = myDataBase.rawQuery("Select g.*, f.name, h.name from origin h, " +
		//			"genus g, family f where f.id = g.familyid and h.id = g.originid   ", null);
			c.moveToFirst();
			
			if (c != null) {
				do {
					man = man + c.getString(1) + newline;
					man = man + "     Growing ease: "+ c.getInt(3)+ newline;
					man = man + "     Flowering: " + c.getString(4) + newline;
					man = man + "     Foliage: " + c.getString(5) + newline;
					man = man + "     Description: " + c.getString(6) + newline;
					man = man + newline;
					
				} while (c.moveToNext());
			}
		return man;
	}
	
	public String getFamilyName( int id){
		Cursor c = myDataBase.rawQuery("DECLARE " +
				"id NUMBER;"+
				"salariu VARCHAR"+
				"BEGIN"+
				"id := " + id +
				"identificare(id,salariu",null);
		c.moveToFirst();
		return c.getString(0);
	}
	
	public String getGenusInfo(){
		String man = "";
		String newline = System.getProperty("line.separator");
		Cursor c = myDataBase.rawQuery("Select g.*, f.name, h.name from origin h, " +
				"genus g, family f where f.id = g.familyid and h.id = g.originid   ", null);
		c.moveToFirst();
		
		if (c != null) {
			do {
				man = man + c.getString(1) + newline;
				man = man + "     Family: "+ c.getString(8)+ newline;
				man = man + "     Origin: " + c.getString(9) + newline;
				man = man + "     Soil Type: " + c.getString(4) + newline;
				man = man + "     Light: " + c.getString(5) + newline;
				man = man + "     Temp: " + c.getString(6)+ newline;
				man = man + "     Water: " + c.getString(7) + newline;
				man = man + "     Family name: "+ c.getString(8)+ newline;
				man = man + newline;
				
			} while (c.moveToNext());
		}
		return man;
	}
    
}
